package com.lumlord.util;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.annotation.AnnotatedElementUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import com.lumlord.common.annotation.mybatis.Master;
import com.lumlord.common.annotation.mybatis.SqlInjectionType;
import com.lumlord.common.annotation.pojo.IgnoreColumn;
import com.lumlord.common.annotation.pojo.PK;
import com.lumlord.common.annotation.pojo.Table;
import com.lumlord.common.exception.BusinessException;
import com.lumlord.common.log.Log;
import com.lumlord.common.log.LogFactory;
import com.lumlord.common.mybatis.SetColumnVal;

import cn.hutool.core.bean.BeanUtil;

/**
 * 简易jdbc
 *
 * @author gpf
 */
@Component
public class JdbcTemplateUtils {

    /**
     * 判断object是否为基本类型
     *
     * @param className
     * @return
     */
    private boolean isBaseType(Class className) {

        if (className.equals(java.lang.Integer.class) ||
                className.equals(java.lang.Byte.class) ||
                className.equals(java.lang.Long.class) ||
                className.equals(java.lang.Double.class) ||
                className.equals(java.lang.Float.class) ||
                className.equals(java.lang.Character.class) ||
                className.equals(java.lang.Short.class) ||
                className.equals(java.lang.Boolean.class)) {
            return true;
        }
        return false;
    }


    private String listToString(List list) {
        StringBuffer sb = new StringBuffer();
        for (Object o : list) {
            sb.append(o + ",");
        }
        sb.deleteCharAt(sb.toString().length() - 1);
        return sb.toString();
    }

    public <T> List<T> queryList(String sql, Class<T> clazz, Object... args) {
        List<T> newList = null;

        for (int index = 0; index < args.length; index++) {
            if (args[index] instanceof List) {
                List l = (List) args[index];
                args[index] = listToString(l);
            }
        }

        try {
            if (isBaseType(clazz)) {
                List<T> list = jdbc.queryForList(sql, clazz, args);
                return list;
            }

            boolean isMap = clazz.getName().toLowerCase().indexOf("map") != -1;

            List<Map<String, Object>> result = jdbc.queryForList(sql, Arrays.asList(args).toArray());
            newList = new ArrayList<T>(result.size());
            for (Map<String, Object> map : result) {
                if (isMap) {
                    T o = (T) formatHumpName(map);
                    newList.add(o);
                } else {
                    newList.add(BeanUtil.mapToBean(map, clazz, true));
                }

            }
            log(sql);
            log(org.apache.commons.lang.StringUtils.join(Arrays.asList(args), ','));
        } catch (Exception e) {
            log(e.getMessage(), e);
            return new ArrayList<T>(0);
        }
        log("size = " + newList.size());
        return newList;

    }


    public static String toFormatCol(String colName) {
        StringBuilder sb = new StringBuilder();
        String[] str = colName.toLowerCase().split("_");
        int i = 0;
        for (String s : str) {
            if (s.length() == 1) {
                s = s.toUpperCase();
            }
            i++;
            if (i == 1) {
                sb.append(s);
                continue;
            }
            if (s.length() > 0) {
                sb.append(s.substring(0, 1).toUpperCase());
                sb.append(s.substring(1));
            }
        }
        return sb.toString();
    }

    /**
     * 将Map中的key由下划线转换为驼峰
     *
     * @param map
     * @return
     */
    public static Map<String, Object> formatHumpName(Map<String, Object> map) {
        Map<String, Object> newMap = new HashMap<String, Object>();
        Iterator<Map.Entry<String, Object>> it = map.entrySet().iterator();
        while (it.hasNext()) {
            Map.Entry<String, Object> entry = it.next();
            String key = entry.getKey();
            String newKey = toFormatCol(key);
            newMap.put(newKey, entry.getValue());
        }
        return newMap;
    }

    public <T> T queryOne(String sql, Class<T> clazz, Object... args) throws DataAccessException {
        List<T> list = queryList(sql, clazz, args);
        if (list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    public <T> List<T> queryList(Object obj) throws BusinessException {
        List parameters = new ArrayList();
        String sql = selectNotNull(obj, parameters);
        Object[] objects = new Object[parameters.size()];
        parameters.toArray(objects);

        List<T> list = (List<T>) queryList(sql, obj.getClass(), objects);
        return list;

    }


    //根據主建查询内容
    public <T> T load(Class<T> clazz, Integer id) throws BusinessException {
        List<Field> allFields = getAllFields(clazz);
        StringBuilder sql = new StringBuilder("SELECT * FROM  ");
        if (AnnotatedElementUtils.hasAnnotation(clazz, Table.class)) {
            Table table = AnnotatedElementUtils.findMergedAnnotation(clazz, Table.class);
            sql.append(getDb(table) + " " + " WHERE ");
        } else {
            throw new BusinessException("un support eg:@Table(\"t_user\") !");
        }
        //	Object  o = null;
        T t = null;
        try {
            t = clazz.newInstance();
        } catch (InstantiationException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        for (Field field : allFields) {
            //主键
            PK pk = field.getAnnotation(PK.class);
            ;
            if (null != pk) {
                sql.append(pk.value() + "=? ");
                Object value = id;
                t = queryOne(sql.toString(), clazz, value);
                return t;
            }
        }
        return t;
    }

    public <T> T queryOne(Object obj) throws BusinessException {
        List parameters = new ArrayList();
        String sql = selectNotNull(obj, parameters);
        Object[] objects = new Object[parameters.size()];
        parameters.toArray(objects);
        List<T> list = (List<T>) queryList(sql, obj.getClass(), objects);
        if (list.size() > 0) {
            return list.get(0);
        }
        return null;
    }


    /**
     * update 封装
     *
     * @param bean 继承 BaseEntity bean
     * @return
     */
    @Master
    public boolean updateById(Object bean) throws BusinessException {
        entityFilter(bean, SqlInjectionType.UPDATE);
        List parameters = new ArrayList();
        String sql = getUpdateSql(bean, parameters);
        log(sql, parameters.toString());
        boolean b = jdbc.update(sql, parameters.toArray()) >= 0;

        return b;
    }

    /**
     * save 封装
     *
     * @param bean 继承 BaseEntity bean
     * @return
     */
    @Master
    public boolean save(Object bean) throws BusinessException {
        entityFilter(bean, SqlInjectionType.INSERT);
        List parameters = new ArrayList();
        String sql = getSaveSql(bean, parameters);
        log(sql, parameters.toString());
        boolean b = jdbc.update(sql, parameters.toArray()) >= 0;
        return b;
    }


    /**
     * saveOrUpdate  封装
     * id 对应数据库存在值 新增 。
     * null或者 无对应 修改。
     *
     * @param bean 继承 BaseEntity bean
     * @return
     */
    @Master
    public boolean saveOrUpdate(Object bean) throws BusinessException {
        boolean isInsert = isInsert(bean);
        List parameters = new ArrayList();
        String sql = null;
        if (isInsert) {
            entityFilter(bean, SqlInjectionType.INSERT);
            sql = getSaveSql(bean, parameters);
        } else {
            entityFilter(bean, SqlInjectionType.UPDATE);
            sql = getUpdateSql(bean, parameters);
        }

        log(sql, parameters.toString());
        boolean b = jdbc.update(sql, parameters.toArray()) >= 0;
        return b;
    }

    /**
     * @param bean
     * @return true 先查是否存在 ，在新增 。 false 修改
     * @throws BusinessException
     */
    private boolean isInsert(Object bean) throws BusinessException {
        Class clazz = bean.getClass();
        List<Field> allFields = getAllFields(clazz);
        Boolean saveOrUpdate = false;
        StringBuilder sql = new StringBuilder("SELECT 1 FROM  ");
        if (AnnotatedElementUtils.hasAnnotation(clazz, Table.class)) {
            Table table = AnnotatedElementUtils.findMergedAnnotation(clazz, Table.class);
            sql.append(getDb(table) + " " + " WHERE ");
        } else {
            throw new BusinessException("un support eg:@Table(\"t_user\") !");
        }

        for (Field field : allFields) {
            //主键
            PK pk = field.getAnnotation(PK.class);
            ;
            if (null != pk) {
                saveOrUpdate = true;
                sql.append(pk.value() + "=? ");
                Object value = BeanUtil.getFieldValue(bean, field.getName());
                if (null == value) { //新增
                    return true;
                } else {
                    Object o = queryOne(sql.toString(), clazz, value);
                    if (null == o) { //新增
                        throw new BusinessException("did't find value [" + value + "] :" + sql.toString());
                    } else { //有值 save or update
                        return false;
                    }
                }
            }
        }

        return true;
    }


    private String getSaveSql(Object bean, List parameters) throws BusinessException {
        Class clazz = bean.getClass();
        List<Field> allFields = getAllFields(clazz);

        StringBuilder sql = new StringBuilder("INSERT INTO  ");
        StringBuffer sqlValues = new StringBuffer("(");
        if (AnnotatedElementUtils.hasAnnotation(clazz, Table.class)) {
            Table table = AnnotatedElementUtils.findMergedAnnotation(clazz, Table.class);
            sql.append(getDb(table) + " " + "( ");
        } else {
            throw new BusinessException("un support eg:@Table(\"t_user\") !");
        }

        //设置字段
        for (Field field : allFields) {
            String fieldName = field.getName();
            Object value = BeanUtil.getFieldValue(bean, fieldName);
            if (fieldName.startsWith("$")) {
                continue;
            }
            if (null == value) {
                continue;
            }


            //设置忽略
            IgnoreColumn ignoreColumn = field.getAnnotation(IgnoreColumn.class);
            if (null != ignoreColumn) {
                continue;
            }

            //主键
            PK pk = field.getAnnotation(PK.class);
            ;
            if (null != pk) {
                continue;
            }
            sql.append(getColumn(new StringBuffer(fieldName)) + ",");
            sqlValues.append("?,");
            parameters.add(value);
        }


        if (sql.toString().endsWith(",")) {
            sql.deleteCharAt(sql.length() - 1);
            sqlValues.deleteCharAt(sqlValues.length() - 1);
        } else {
            throw new BusinessException("无效对象：" + bean.getClass());
        }

        sql.append(") VALUES ");
        sql.append(sqlValues.toString() + " )");

        return sql.toString();
    }


    private String selectNotNull(Object bean, List parameters) throws BusinessException {
        Class clazz = bean.getClass();
        List<Field> allFields = getAllFields(clazz);

        StringBuilder sql = new StringBuilder("SELECT * FROM ");


        if (AnnotatedElementUtils.hasAnnotation(clazz, Table.class)) {
            Table table = AnnotatedElementUtils.findMergedAnnotation(clazz, Table.class);
            sql.append(getDb(table) + " " + "WHERE ");
        } else {
            throw new BusinessException("un support eg:@Table(\"t_user\") !");
        }

        //设置字段
        for (Field field : allFields) {
            String fieldName = field.getName();
            Object value = BeanUtil.getFieldValue(bean, fieldName);

            if (null == value) {
                continue;
            }


            //设置忽略
            IgnoreColumn ignoreColumn = field.getAnnotation(IgnoreColumn.class);
            if (null != ignoreColumn) {
                continue;
            }

            //sql.append(getColumn(new StringBuffer(fieldName)) + "=:" + fieldName + ",");

            sql.append(" " + getColumn(new StringBuffer(fieldName)) + "=?" + " AND");
            parameters.add(value);
        }

        if (sql.toString().endsWith("AND")) {
            sql.delete(sql.length() - 3, sql.length());
        } else {
            throw new BusinessException("无效对象：" + bean.getClass());
        }

        return sql.toString();
    }

    private String getUpdateSql(Object bean, List parameters) throws BusinessException {
        Class clazz = bean.getClass();
        List<Field> allFields = getAllFields(clazz);

        StringBuilder sql = new StringBuilder("UPDATE ");
        // 字段 ，数据库列名
        Map<String, Object> pks = new HashMap<String, Object>();

        if (AnnotatedElementUtils.hasAnnotation(clazz, Table.class)) {
            Table table = AnnotatedElementUtils.findMergedAnnotation(clazz, Table.class);
            sql.append(getDb(table) + " " + "SET ");
        } else {
            throw new BusinessException("un support eg:@Table(\"t_user\") !");
        }

        //设置字段
        for (Field field : allFields) {
            String fieldName = field.getName();
            Object value = BeanUtil.getFieldValue(bean, fieldName);

            if (null == value) {
                continue;
            }


            //设置忽略
            IgnoreColumn ignoreColumn = field.getAnnotation(IgnoreColumn.class);
            if (null != ignoreColumn) {
                continue;
            }

            //主键
            PK pk = field.getAnnotation(PK.class);
            ;
            if (null != pk) {
                pks.put(field.getName(), value);
                continue;
            }

            //sql.append(getColumn(new StringBuffer(fieldName)) + "=:" + fieldName + ",");

            sql.append(getColumn(new StringBuffer(fieldName)) + "=?" + ",");
            parameters.add(value);
        }

        if (sql.toString().endsWith(",")) {
            sql.deleteCharAt(sql.length() - 1);
        } else {
            throw new BusinessException("无效对象：" + bean.getClass());
        }

        //拼装 where
        sql.append(" WHERE ");
        Iterator<String> pkKeys = pks.keySet().iterator();

        if (0 == pks.size()) {
            throw new BusinessException("not fund this bean eg:[@PK(\"id\")]  !");
        }

        while (pkKeys.hasNext()) {
            String key = pkKeys.next();
            sql.append(" " + key + " =?  AND");
            parameters.add(pks.get(key));
        }

        if (sql.toString().endsWith("AND")) {
            sql.delete(sql.length() - 3, sql.length());
        } else {
            throw new BusinessException("无效对象：" + bean.getClass());
        }

        return sql.toString();
    }


    /**
     * 设置 默认值 注解
     */
    public void entityFilter(Object o, SqlInjectionType sqlType) {
        List<Field> fields = getAllFields(o.getClass());
        String SQL = sqlType.name().toUpperCase();
        for (Field field : fields) {
            SetColumnVal.execute(field, o, SQL, sqlType);
        }
    }


    /***
     * 注解设置到对象 o 可以是实体对象，也可以是 List
     * @param o
     * @param sqlType
     */
    public void fill(Object o, SqlInjectionType sqlType) {
        if (o instanceof List) {
            List list = new ArrayList();
            for (Object e : list) {
                entityFilter(e, sqlType);
            }
        } else {
            entityFilter(o, sqlType);
        }
    }

    /**
     * 字段声明
     *
     * @param clazz
     * @return
     */
    private List<Field> getAllFields(Class clazz) {
        List<Field> list = new ArrayList<Field>();
        Field[] fieldsParent = clazz.getSuperclass().getDeclaredFields();
        for (Field field : fieldsParent) {
            list.add(field);
        }
        Field[] fieldsChild = clazz.getDeclaredFields();
        for (Field field : fieldsChild) {
            list.add(field);
        }

        return list;
    }

    /**
     * 获取 数据库
     *
     * @param table
     * @return
     */
    private String getDb(Table table) {
		/*if(table.DB().toUpperCase().equals("SYS")) {
			return db+"."+table.value();
		}else {
			return businessDb+"."+table.value();
		}*/
        return table.DB() + "." + table.value();
    }


    /**
     * 驼峰转下划线
     *
     * @param str
     * @return
     */
    public String getColumn(StringBuffer str) {
        Pattern pattern = Pattern.compile("[A-Z]");
        Matcher matcher = pattern.matcher(str);
        StringBuffer sb = new StringBuffer(str);
        if (matcher.find()) {
            sb = new StringBuffer();
            // 将当前匹配子串替换为指定字符串，并且将替换后的子串以及其之前到上次匹配子串之后的字符串段添加到一个StringBuffer对象里。
            // 正则之前的字符和被替换的字符
            matcher.appendReplacement(sb, "_" + matcher.group(0).toLowerCase());
            // 把之后的也添加到StringBuffer对象里
            matcher.appendTail(sb);
        } else {
            return sb.toString();
        }
        return getColumn(sb);
    }

    private void log(String msg) {
        if (jdbcLog) {
            log.info(msg);
        }
    }

    private void log(String format, Object arg) {
        if (jdbcLog) {
            log.info(format, arg);
        }
    }

    private Log log = new LogFactory(getClass());

    @Value("${lumlord.log:false}")
    private boolean jdbcLog;


    @Value("${lumlord.business.db:}")
    private String businessDb;


    @Autowired
    private JdbcTemplate jdbc;
}
